package W071.src.dao;

import W071.src.entity.Department;
import W071.src.entity.Teacher;
import W071.src.util.Condition;
import W071.src.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class TeacherDao {
    private static TeacherDao instance = new TeacherDao();

    private TeacherDao() {
    }

    public static TeacherDao getInstance() {
        return instance;
    }

    // 查询所有
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList) throws SQLException {
        Collection<Teacher> desiredTeachers = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher ");
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection, "teacher");
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while (resultSet_select.next()) {
            Long id = resultSet_select.getLong("id");
            String name = resultSet_select.getString("name");
            int departmentId = resultSet_select.getInt("department_id");
            Department department = DepartmentDaoImpl.getInstance().find(Long.valueOf(departmentId));
            Teacher desiredTeacher = new Teacher(id, name, department);
            desiredTeachers.add(desiredTeacher);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredTeachers;
    }

    public static void main(String[] args) {
        TeacherDao teacherDao = TeacherDao.getInstance();
        try {
            Connection connection = JdbcHelper.getConn();
            Collection<Teacher> teachers = teacherDao.findAll(connection, null);
            for (Teacher teacher : teachers) {
                System.out.println(teacher);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // ID 查询
    public Teacher findById(Connection connection, Long id) throws SQLException {
        String sql = "SELECT * FROM teacher WHERE id = ?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setLong(1, id);
        ResultSet resultSet = pstmt.executeQuery();
        Teacher teacher = null;
        if (resultSet.next()) {
            String name = resultSet.getString("name");
            int departmentId = resultSet.getInt("department_id");
            Department department = DepartmentDaoImpl.getInstance().find(id);
            teacher = new Teacher(id, name, department);
        }
        JdbcHelper.close(resultSet, pstmt);
        return teacher;
    }

    // 增
    public int add(Connection connection, Teacher teacher) throws SQLException {
        String sql = "INSERT INTO teacher (name, department_id) VALUES (?, ?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, teacher.getName());
        pstmt.setLong(2, teacher.getDepartment().getId());
        int rows = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rows;
    }

    // 改
    public int update(Connection connection, Teacher teacher) throws SQLException {
        String sql = "UPDATE teacher SET name = ?, department_id = ? WHERE id = ?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, teacher.getName());
        pstmt.setLong(2, teacher.getDepartment().getId());
        pstmt.setLong(3, teacher.getId());
        int rows = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rows;
    }

    // 删
    public int delete(Connection connection, Long id) throws SQLException {
        String sql = "DELETE FROM teacher WHERE id = ?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setLong(1, id);
        int rows = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rows;
    }
}